package cn.whit.blog.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.whit.blog.dao.TcommitDao;
import cn.whit.blog.domain.Tcommit;
import cn.whit.blog.util.DynamicQuery;
import cn.whit.blog.util.JDBCUtils;
import cn.whit.blog.util.Parameter;

public class TcommitDaoImpl implements TcommitDao{

	@Override
	public Tcommit findById(String id) throws SQLException {
		String sql="select * from tcommit where id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Tcommit>(Tcommit.class),id);
	}

	@Override
	public List<Tcommit> findAll() throws SQLException {
		String sql="select * from tcommit";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Tcommit>(Tcommit.class));
	}
	
	@Override
	public int findTotalRecords() throws Exception {
		String sql="select count(*) from tcommit";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}
	
	@Override
	public List<Tcommit> findAllWithPage(int startIndex, int pageSize) throws Exception {
		String sql="select * from tcommit order by id desc limit  ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Tcommit>(Tcommit.class),startIndex,pageSize);
		
	}
	
	@Override
	public int findTotalRecordsAndQuery(DynamicQuery query) throws Exception {
		String sql="select count(*) from tcommit where 1=1 ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size()];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		Long num=(Long)qr.query(sql, new ScalarHandler(),objs);
		return num.intValue();
	}
	
	@Override
	public List<Tcommit> findAllWithPageAndQuery(int startIndex, int pageSize,DynamicQuery query) throws Exception {
		String sql="select * from tcommit where 1=1 ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		sql += " order by id desc limit  ? , ? ";
		
        //如果想排序，自行在sql后添加   
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size() + 2];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		objs[i] = startIndex;
		objs[i+1] = pageSize;
		
		return qr.query(sql, new BeanListHandler<Tcommit>(Tcommit.class),objs);
	}

	@Override
	public List<Tcommit> selectByQuery(DynamicQuery query) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void save(Tcommit tcommit) throws SQLException {
		String sql="INSERT INTO tcommit(content,publishTime,userid,username,blogid,commituserid,commitusername) "
				+ " VALUES(?,?,?,?,?,?,?)";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={tcommit.getContent(),tcommit.getPublishTime(),tcommit.getUserid(),tcommit.getUsername(),
				tcommit.getBlogid(),tcommit.getCommituserid(),tcommit.getCommitusername()};
		qr.update(sql,params);
	}

	@Override
	public List<Tcommit> findAllByBlogIdWithPageAndQuery(int startIndex, int pageSize, DynamicQuery query, String blogid) throws SQLException { 
		String sql="select * from tcommit where 1=1 and blogid=" +blogid + " ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		sql += " order by id desc limit  ? , ? ";
		
        //如果想排序，自行在sql后添加   
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size() + 2];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		objs[i] = startIndex;
		objs[i+1] = pageSize;
		
		return qr.query(sql, new BeanListHandler<Tcommit>(Tcommit.class),objs);
	}

	@Override
	public int findTotalRecordsByBlogIdAndQuery(DynamicQuery query, String blogid) throws SQLException {
		String sql="select count(*) from tcommit where 1=1 and blogid=" + blogid;
		query.setBaseSql(sql);
		sql = query.generateSql();
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size()];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		Long num=(Long)qr.query(sql, new ScalarHandler(),objs);
		return num.intValue();
	}

	@Override
	public void deleteByVoteId(String s) throws SQLException {
		String sql="delete from tcommit where blogid=? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={s};
		qr.update(sql,params);
	}
	
}
